[Redshift] ウィンドウ関数：NTILEでデシル分析を行い商品別の販売比率を表示する

#データベース

takahara

2016.12.08

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

分析について学習していてデシル分析に出会いました。とても有用に感じたので学習結果を記しておきます。

環境

Mac OSX 10.10.5
Redshift 1.0.1125

デシル分析とは

対象データを10等分（ラテン語でデシル）にして売上の割合などを分析する手法の様です。

準備

CREATE SCHEMA IF NOT EXISTS blog;
DROP TABLE IF EXISTS blog.pre_decil;
CREATE TABLE blog.pre_decil (
  id integer
  , user_id integer
  , item_id integer
  , sales_date date
  , price integer
  , quantity integer
);
INSERT INTO blog.pre_decil
VALUES
   ( 1,333, 3,'2016-12-01',2000,5)
  ,( 2,222, 2,'2016-10-01',1500,6)
  ,( 3,111, 7,'2016-06-01',5000,1)
  ,( 4,222, 5,'2016-03-01',3000,3)
  ,( 5,333, 6,'2015-11-01',4000,4)
  ,( 6,111, 4,'2015-07-01',2500,2)
  ,( 7,111, 1,'2015-02-01',1000,3)
  ,( 8,111, 9,'2014-10-01',6000,1)
  ,( 9,333, 3,'2014-09-01',2000,2)
  ,(10,111, 1,'2014-03-01',1000,2)
  ,(11,111, 1,'2013-10-01',1000,2)
  ,(12,111, 8,'2013-03-01',5500,1)
  ,(13,333, 1,'2012-12-01',1000,3)
  ,(14,222, 4,'2012-10-01',2500,1)
  ,(15,333,10,'2012-09-01',7000,2)
  ,(16,111, 2,'2012-06-01',1500,2)
  ,(17,333, 6,'2011-11-01',4000,5)
  ,(18,333, 3,'2011-09-01',2000,6)
  ,(19,111, 4,'2011-07-01',2500,7)
  ,(20,222, 5,'2011-03-01',3000,1)
  ,(21,111, 7,'2011-02-01',5000,2)
  ,(22,111, 9,'2011-02-01',6000,1)
  ,(23,111, 3,'2010-10-01',2000,3)
  ,(24,111, 8,'2010-07-01',5500,2)
  ,(25,111, 5,'2010-03-01',3000,5);

testdb=# select * from blog.pre_decil order by id;
 id | user_id | item_id | sales_date | price | quantity 
----+---------+---------+------------+-------+----------
  1 |     333 |       3 | 2016-12-01 |  2000 |        5
  2 |     222 |       2 | 2016-10-01 |  1500 |        6
  3 |     111 |       7 | 2016-06-01 |  5000 |        1
  4 |     222 |       5 | 2016-03-01 |  3000 |        3
  5 |     333 |       6 | 2015-11-01 |  4000 |        4
  6 |     111 |       4 | 2015-07-01 |  2500 |        2
  7 |     111 |       1 | 2015-02-01 |  1000 |        3
  8 |     111 |       9 | 2014-10-01 |  6000 |        1
  9 |     333 |       3 | 2014-09-01 |  2000 |        2
 10 |     111 |       1 | 2014-03-01 |  1000 |        2
 11 |     111 |       1 | 2013-10-01 |  1000 |        2
 12 |     111 |       8 | 2013-03-01 |  5500 |        1
 13 |     333 |       1 | 2012-12-01 |  1000 |        3
 14 |     222 |       4 | 2012-10-01 |  2500 |        1
 15 |     333 |      10 | 2012-09-01 |  7000 |        2
 16 |     111 |       2 | 2012-06-01 |  1500 |        2
 17 |     333 |       6 | 2011-11-01 |  4000 |        5
 18 |     333 |       3 | 2011-09-01 |  2000 |        6
 19 |     111 |       4 | 2011-07-01 |  2500 |        7
 20 |     222 |       5 | 2011-03-01 |  3000 |        1
 21 |     111 |       7 | 2011-02-01 |  5000 |        2
 22 |     111 |       9 | 2011-02-01 |  6000 |        1
 23 |     111 |       3 | 2010-10-01 |  2000 |        3
 24 |     111 |       8 | 2010-07-01 |  5500 |        2
 25 |     111 |       5 | 2010-03-01 |  3000 |        5
(25 rows)

カラムについて。
id = レコードのid
user_id = ユーザーid
item_id = 商品ID
sales_date = 販売日
price = 単価
quantity = 売上数

構文（ウィンドウ関数：NTILE）

Amazon Redshift | NTILE ウィンドウ関数

NTILE (expr) OVER ([ PARTITION BY expression_list ]
                   [ ORDER BY order_list ])

expr に何等分するかを指定。
PARTITION BY でグループを指定。
ORDER BY で対象カラムの昇順・降順を指定。

使い方

目的

単価と売上数からを売上金額の高い順に10分割する。

SQL

SELECT
  user_id
  , item_id
  , price
  , quantity
  , price * quantity AS amount
  , NTILE(10) OVER(PARTITION BY user_id ORDER BY (price * quantity) DESC) AS decil
FROM
  blog.pre_decil
ORDER BY
  user_id;

実行結果

.
 user_id | item_id | price | quantity | amount | decil 
---------+---------+-------+----------+--------+-------
     111 |       4 |  2500 |        7 |  17500 |     1
     111 |       5 |  3000 |        5 |  15000 |     1
     111 |       8 |  5500 |        2 |  11000 |     2
     111 |       7 |  5000 |        2 |  10000 |     2
     111 |       9 |  6000 |        1 |   6000 |     3
     111 |       9 |  6000 |        1 |   6000 |     3
     111 |       3 |  2000 |        3 |   6000 |     4
     111 |       8 |  5500 |        1 |   5500 |     4
     111 |       4 |  2500 |        2 |   5000 |     5
     111 |       7 |  5000 |        1 |   5000 |     6
     111 |       2 |  1500 |        2 |   3000 |     7
     111 |       1 |  1000 |        3 |   3000 |     8
     111 |       1 |  1000 |        2 |   2000 |     9
     111 |       1 |  1000 |        2 |   2000 |    10
     222 |       2 |  1500 |        6 |   9000 |     1
     222 |       5 |  3000 |        3 |   9000 |     2
     222 |       5 |  3000 |        1 |   3000 |     3
     222 |       4 |  2500 |        1 |   2500 |     4
     333 |       6 |  4000 |        5 |  20000 |     1
     333 |       6 |  4000 |        4 |  16000 |     2
     333 |      10 |  7000 |        2 |  14000 |     3
     333 |       3 |  2000 |        6 |  12000 |     4
     333 |       3 |  2000 |        5 |  10000 |     5
     333 |       3 |  2000 |        2 |   4000 |     6
     333 |       1 |  1000 |        3 |   3000 |     7
(25 rows)

ORDER BYにuser_idを指定しただけですが、それ以下はウィンドウ関数のORDER BYで指定した順に並び替えられて表示されてます。

デシル分析を行う

目的

商品別の売上金額の比率を作成してデシル分析する。

SQL

-- 5,売上金額（amount）と比率（percentage）の累積（amount_cum, percent_cum）を作成。
--   全カラム順を整える。
SELECT
  item_id
  , price
  , amount
  , SUM(amount) OVER(ORDER BY decil 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                     ) AS amount_cum
  , amount_total
  , percentage
  , SUM(percentage) OVER(ORDER BY decil 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                         ) AS percent_cum
  , decil
FROM
 (
  -- 4,売上金額の比率でデシル分析。
  SELECT
    *
    , NTILE(10) OVER(ORDER BY percentage DESC) AS decil
  FROM
   (
    -- 3,売上金額（amount）と総売上金額（amount_total）から売上金額の比率を作成。
    SELECT
      *
      , CAST((CAST(amount AS FLOAT) / CAST(amount_total AS FLOAT) * 100)
             AS DECIMAL(5,2)
             ) AS percentage
    FROM
     (
      -- 2,単価の数から販売数（quantity）、商品別の売上金額（amount）、総売上金額（amount_total）を作成。
      SELECT
        item_id
        , price
        , quantity
        , price * quantity AS amount
        , SUM(price * quantity) 
          OVER(ORDER BY (price * quantity) 
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
               ) AS amount_total
      FROM
       (
        -- 1,商品ごとに単価と売上数を集計。
        SELECT
          item_id
          , SUM(price) AS price
          , SUM(quantity) AS quantity
        FROM blog.pre_decil
        GROUP BY item_id
       )
      GROUP BY item_id, price, quantity
     )
   )
 );

1〜5までを順に処理しています。
何をしているか詳細を知りたければ、段階ごとに実行して確認してみてください。

実行結果

.
 item_id | price | amount | amount_cum | amount_total | percentage | percent_cum | decil 
---------+-------+--------+------------+--------------+------------+-------------+-------
       3 |  8000 | 128000 |     128000 |       521000 |      24.57 |       24.57 |     1
       5 |  9000 |  81000 |     209000 |       521000 |      15.55 |       40.12 |     2
       4 |  7500 |  75000 |     284000 |       521000 |      14.40 |       54.52 |     3
       6 |  8000 |  72000 |     356000 |       521000 |      13.82 |       68.34 |     4
       1 |  4000 |  40000 |     396000 |       521000 |       7.68 |       76.02 |     5
       8 | 11000 |  33000 |     429000 |       521000 |       6.33 |       82.35 |     6
       7 | 10000 |  30000 |     459000 |       521000 |       5.76 |       88.11 |     7
       9 | 12000 |  24000 |     483000 |       521000 |       4.61 |       92.72 |     8
       2 |  3000 |  24000 |     507000 |       521000 |       4.61 |       97.33 |     9
      10 |  7000 |  14000 |     521000 |       521000 |       2.69 |      100.02 |    10
(10 rows)

上位３項目で50%を超える売上となっている事が分かりました。